﻿CREATE VIEW dbo.VIEW_SEARCH_PU_FOR_COST_PROTOCOL
AS
SELECT     dbo.PRODUCT_UNIT.NAME, dbo.PRODUCT_UNIT.TYPE, COUNT(dbo.PRODUCT_UNIT.NAME) AS COUNT, dbo.NSI_METER.SHORT_NAME AS METER, 
                      dbo.CP_STAGE.NAME AS CAL_P_NAME, dbo.CALENDAR_PLAN.NUMBER + N' \ №' + SPACE(1) + dbo.CP_STAGE.NUMBER + SPACE(1) 
                      + dbo.CP_STAGE.NAME AS CAL_P_NUMBER, dbo.CP_STAGE.START_DATE AS CAL_P_START_DATE, dbo.CP_STAGE.END_DATE AS CAL_P_END_DATE, 
                      dbo.PRODUCT_UNIT.PID, 1 AS COUNT_TMP, dbo.PRODUCT_UNIT.CP_STAGE_ID
FROM         dbo.CALENDAR_PLAN INNER JOIN
                      dbo.CP_STAGE INNER JOIN
                      dbo.PRODUCT_UNIT ON dbo.CP_STAGE.ID = dbo.PRODUCT_UNIT.CP_STAGE_ID ON 
                      dbo.CALENDAR_PLAN.ID = dbo.CP_STAGE.CALENDAR_PLAN_ID LEFT OUTER JOIN
                      dbo.NSI_METER RIGHT OUTER JOIN
                      dbo.NSI_PRODUCT ON dbo.NSI_METER.ID = dbo.NSI_PRODUCT.NSI_METER_ID ON dbo.PRODUCT_UNIT.NSI_PRODUCT_ID = dbo.NSI_PRODUCT.ID
WHERE     (NOT (dbo.PRODUCT_UNIT.ID IN
                          (SELECT     PRODUCT_UNIT_ID
                            FROM          dbo.PRODUCT_COST AS PC)))
GROUP BY dbo.PRODUCT_UNIT.TYPE, dbo.PRODUCT_UNIT.NAME, dbo.NSI_METER.SHORT_NAME, dbo.CP_STAGE.NAME, dbo.CALENDAR_PLAN.NUMBER + N' \ №' + SPACE(1)
                       + dbo.CP_STAGE.NUMBER + SPACE(1) + dbo.CP_STAGE.NAME, dbo.CP_STAGE.START_DATE, dbo.CP_STAGE.END_DATE, dbo.PRODUCT_UNIT.PID, 
                      dbo.PRODUCT_UNIT.CP_STAGE_ID